package com.weidong.poidemo.service;

import com.weidong.poidemo.dao.ExcelDao;
import com.weidong.poidemo.entity.Excel;
import com.weidong.poidemo.util.DownloadUtils;
import com.weidong.poidemo.util.ExcelExportUtil;
import com.weidong.poidemo.util.ExcelImportUtil;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.core.io.ClassPathResource;
import org.springframework.core.io.Resource;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.ByteArrayOutputStream;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.*;

/**
 * @author weidong
 * @version 1.0
 * @date 2017/11/18 14:32
 * @description
 */
@Service
public class ExcelService {

    @Autowired
    private ExcelDao excelDao;

    /**
     * 通过工具类导出
     * @param response
     */
    public void excelExportUtil(HttpServletResponse response) throws Exception {
        //1.获取报表数据
        final List<Excel> all = excelDao.findAll();
        //2.加载模板
        final Resource resource = new ClassPathResource("excel-template/user-export-template.xlsx");
        //2.0读取excel
        final FileInputStream fis = new FileInputStream(resource.getFile());
        //2.1通过工具类完成下载
        new ExcelExportUtil(Excel.class,2,2).
             export(response,fis,all,"用户.xlsx");

    }

    /**
     * 导出表格
     */
    public void export(HttpServletResponse response) throws Exception{
        //1.获取报表数据
        final List<Excel> all = excelDao.findAll();
        //2.构造excel,创建工作簿,设置内存中对象最大数量
        //Workbook wb = new XSSFWorkbook();SXSSFWorkbook支持百万数据
        SXSSFWorkbook wb = new SXSSFWorkbook();
        //2.0 设置样式
        final CellStyle cellStyle = wb.createCellStyle();
        cellStyle.setBorderTop(BorderStyle.THIN);
        //上边框
        cellStyle.setBorderBottom(BorderStyle.THIN);
        //下边框
        cellStyle.setBorderLeft(BorderStyle.THIN);
        //左边框
        cellStyle.setBorderRight(BorderStyle.THIN);
        //右边框
        //2.1 设置文字
        final Font font = wb.createFont();
        font.setFontName("华文行楷");
        //字体
        font.setFontHeightInPoints((short)16);
        //字号
        cellStyle.setFont(font);

        //剧中显示
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        //水平居中
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        //垂直居中

        //3.构造sheet
        Sheet sheet = wb.createSheet();
        //4.设置标题
        String [] titles = "编号,姓名,年龄,日期".split(",");
        //5.处理标题
        Row row = sheet.createRow(0);

        int titleIndex = 0;
        for (String title : titles) {
            final Cell cell = row.createCell(titleIndex++);
            cell.setCellStyle(cellStyle);
            cell.setCellValue(title);
        }

        //6.处理内容
        int rowIndex = 1;
        Cell cell = null;
        //用户测试大数据量导出
        for (int i = 0; i < 10000; i++) {
            System.out.println("--------------------");
            for (Excel excel : all) {
                final Row row1 = sheet.createRow(rowIndex++);
                //编号
                cell = row1.createCell(0);
                cell.setCellValue(excel.getId());
                cell.setCellStyle(cellStyle);
                //姓名
                cell = row1.createCell(1);
                cell.setCellValue(excel.getName());
                cell.setCellStyle(cellStyle);
                //年龄
                cell = row1.createCell(2);
                cell.setCellValue(excel.getAge());
                cell.setCellStyle(cellStyle);
                //生日
                cell = row1.createCell(3);
                cell.setCellValue(excel.getBirthday());
                cell.setCellStyle(cellStyle);
            }
        }
        //7:完成下载
        ByteArrayOutputStream os = new ByteArrayOutputStream();
        wb.write(os);
        new DownloadUtils().download(os,response,"人员报表.xlsx");

    }
    /**
     * 导出表格
     */
    public void exportTemple(HttpServletResponse response) throws Exception{
        //1.获取报表数据
        final List<Excel> all = excelDao.findAll();
        //2.加载模板
        final Resource resource = new ClassPathResource("excel-template/user-export-template.xlsx");
        //2.0读取excel
        final FileInputStream fis = new FileInputStream(resource.getFile());
        //3.根据模板创建工作簿
        Workbook wb = new XSSFWorkbook(fis);
        //4.读取工作表第一页
        final Sheet sheet = wb.getSheetAt(0);
        //5.抽取公共的样式
        final Row row = sheet.getRow(2);
        CellStyle styles[] = new CellStyle[row.getLastCellNum()];
        for(int i=0;i<row.getLastCellNum();i++) {
            Cell cell = row.getCell(i);
            styles[i] = cell.getCellStyle();
        }
        //6.处理内容
        int rowIndex = 1;
        Cell cell = null;
        for (Excel excel : all) {
            final Row row1 = sheet.createRow(rowIndex++);
            //编号
            cell =  row1.createCell(0);
            cell.setCellValue(excel.getId());
            cell.setCellStyle(styles[0]);
            //姓名
            cell = row1.createCell(1);
            cell.setCellValue(excel.getName());
            cell.setCellStyle(styles[1]);
            //年龄
            cell = row1.createCell(2);
            cell.setCellValue(excel.getAge());
            cell.setCellStyle(styles[2]);
            //生日
            cell = row1.createCell(3);
            cell.setCellValue(excel.getBirthday());
            cell.setCellStyle(styles[3]);
        }
        //7:完成下载
        ByteArrayOutputStream os = new ByteArrayOutputStream();
        wb.write(os);
        new DownloadUtils().download(os,response,"人员报表.xlsx");
    }
    /**
     * 保存对象
     */
    public void save(MultipartFile file) throws Exception {

        //Assert.isNull(file,"The file cannot be empty");
        //1. 解析Excel
        //1.1.根据Excel文件创建工作簿
        final Workbook wb = new XSSFWorkbook(file.getInputStream());
        //1.2 获取Sheet
        //参数：索引
        Sheet sheet = wb.getSheetAt(0);
        //1.3.获取Sheet中的每一行，和每一个单元格3 12
        System.out.println("===============:"+sheet.getLastRowNum());
        List<Excel> list = new ArrayList<>();
        //2.获取用户数据列表
        for (int rowNum = 1; rowNum<= sheet.getLastRowNum()-1 ;rowNum ++) {
            System.out.println("=================================================");
            //根据索引获取每一个行
            Row row = sheet.getRow(rowNum);
            StringBuilder sb = new StringBuilder();
            Object [] values = new Object[row.getLastCellNum()];
            for(int cellNum=1;cellNum< row.getLastCellNum(); cellNum ++) {
                //根据索引获取每一个单元格
                Cell cell = row.getCell(cellNum);
                //获取每一个单元格的内容
                Object value = getCellValue(cell);
                values[cellNum] = value;
                sb.append(value).append("-");
            }
            Excel excel = new Excel(values);
            list.add(excel);
        }
        excelDao.saveAll(list);

    }
    public static Object getCellValue(Cell cell) {
        //1.获取到单元格的属性类型
        CellType cellType = cell.getCellType();
        //2.根据单元格数据类型获取数据
        Object value = null;
        switch (cellType) {
            case STRING:
                value = cell.getStringCellValue();
                break;
            case BOOLEAN:
                value = cell.getBooleanCellValue();
                break;
            case NUMERIC:
                if(DateUtil.isCellDateFormatted(cell)) {
                    //日期格式
                    value = cell.getDateCellValue();
                }else{
                    //数字
                    value = cell.getNumericCellValue();
                }
                break;
            //公式
            case FORMULA:
                value = cell.getCellFormula();
                break;
            default:
                break;
        }
        return value;
    }


    /**
     * excel 导入
     * @param response
     */
    public void importExcel(HttpServletResponse response,MultipartFile file) throws IOException {
        final List list = new ExcelImportUtil(Excel.class)
                .readExcel(file.getInputStream(), 2, 1);
        excelDao.saveAll(list);
    }


}
